
package com.akupilih.app.model;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.akupilih.app.entity.E_Berita;
import com.akupilih.app.entity.E_Candidate;
import com.akupilih.app.entity.E_Event;
import com.akupilih.app.entity.E_Janji;
import com.akupilih.app.entity.E_Organisasi;
import com.akupilih.app.entity.E_Pekerjaan;
import com.akupilih.app.entity.E_Pendidikan;
import com.akupilih.app.entity.E_Penghargaan;

public class DBClass extends SQLiteOpenHelper {

    private static final int DB_VERSION = 2;
    private static final String DB_NAME = "akupilih";
    private static final String TABLE_BERITA = "berita";
    private static final String TABLE_CONFIG = "config";
    private static final String TABLE_VOTE = "vote";
    private static final String TABLE_CANDIDATE = "candidate";
    private static final String TABLE_PENDIDIKAN = "riwayat_pendidikan";
    private static final String TABLE_PEKERJAAN = "riwayat_pekerjaan";
    private static final String TABLE_ORGANISASI = "riwayat_organisasi";
    private static final String TABLE_PENGHARGAAN = "riwayat_penghargaan";
    private static final String TABLE_EVENT = "event";
    private static final String TABLE_JANJI = "janji";

    private static final String KEY_BERITA_ID = "id_berita";
    private static final String KEY_BERITA_URL = "url";
    private static final String KEY_BERITA_TITLE = "title";
    private static final String KEY_BERITA_CONTENT = "content";
    private static final String KEY_BERITA_EXCERPT = "excerpt";
    private static final String KEY_BERITA_DATE = "date";
    private static final String KEY_BERITA_AUTHOR = "author";

    private static final String KEY_CAND_ID = "id";
    private static final String KEY_CAND_NAMA = "nama";
    private static final String KEY_CAND_ROLE = "role";
    private static final String KEY_CAND_JK = "jenis_kelamin";
    private static final String KEY_CAND_AGAMA = "agama";
    private static final String KEY_CAND_TEMPAT_LAHIR = "tempat_lahir";
    private static final String KEY_CAND_TGL_LAHIR = "tgl_lahir";
    private static final String KEY_CAND_STATUS = "status_perkawinan";
    private static final String KEY_CAND_PASANGAN = "nama_pasangan";
    private static final String KEY_CAND_JML_ANAK = "jumlah_anak";
    private static final String KEY_CAND_KELURAHAN = "kelurahan";
    private static final String KEY_CAND_KECAMATAN = "kecamatan";
    private static final String KEY_CAND_KABUPATEN = "kabupaten";
    private static final String KEY_CAND_PROVINSI = "provinsi";
    private static final String KEY_CAND_BIOGRAFI = "biografi";

    private static final String KEY_PEND_ID_CAND = "id_candidate";
    private static final String KEY_PEND_ID = "id";
    private static final String KEY_PEND_RINGKASAN = "ringkasan";
    private static final String KEY_PEND_TGL_MULAI = "tgl_mulai";
    private static final String KEY_PEND_TGL_SELESAI = "tgl_selesai";

    private static final String KEY_PEK_ID_CAND = "id_candidate";
    private static final String KEY_PEK_ID = "id";
    private static final String KEY_PEK_RINGKASAN = "ringkasan";
    private static final String KEY_PEK_TGL_MULAI = "tgl_mulai";
    private static final String KEY_PEK_TGL_SELESAI = "tgl_selesai";

    private static final String KEY_ORG_ID_CAND = "id_candidate";
    private static final String KEY_ORG_ID = "id";
    private static final String KEY_ORG_RINGKASAN = "ringkasan";
    private static final String KEY_ORG_JABATAN = "jabatan";
    private static final String KEY_ORG_TGL_MULAI = "tgl_mulai";
    private static final String KEY_ORG_TGL_SELESAI = "tgl_selesai";

    private static final String KEY_PENG_ID_CAND = "id_candidate";
    private static final String KEY_PENG_ID = "id";
    private static final String KEY_PENG_RINGKASAN = "ringkasan";
    private static final String KEY_PENG_TGL = "tgl";
    private static final String KEY_PENG_INSTITUSI = "institusi";

    private static final String KEY_EVENT_ID = "id";
    private static final String KEY_EVENT_JUDUL = "judul";
    private static final String KEY_EVENT_DESKRIPSI = "deskripsi";
    private static final String KEY_EVENT_TANGGAL = "tanggal_mulai";
    private static final String KEY_EVENT_WAKTU = "waktu_mulai";

    private static final String KEY_JANJI_ID_CAND = "id_calon";
    private static final String KEY_JANJI_CONTEXT = "context_janji";
    private static final String KEY_JANJI = "janji";
    private static final String KEY_JANJI_TGL = "tanggal";
    private static final String KEY_JANJI_SUMBER = "sumber";
    private static final String KEY_CONF_FIRST = "is_pertama";
    private static final String KEY_CONF_VOTE = "voted";

    public DBClass(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

        String create_table_berita = "CREATE TABLE " + TABLE_BERITA + "("
                + KEY_BERITA_ID + " INTEGER PRIMARY KEY, " + KEY_BERITA_URL
                + " TEXT, " + KEY_BERITA_TITLE + " TEXT, " + KEY_BERITA_DATE
                + " TEXT, " + KEY_BERITA_EXCERPT + " TEXT, "
                + KEY_BERITA_CONTENT + " TEXT, " + KEY_BERITA_AUTHOR + " TEXT "
                + ")";

        String create_table_candidate = "CREATE TABLE " + TABLE_CANDIDATE + "("
                + KEY_CAND_ID + " TEXT, " + KEY_CAND_NAMA + " TEXT, "
                + KEY_CAND_ROLE + " TEXT, " + KEY_CAND_JK + " TEXT, "
                + KEY_CAND_AGAMA + " TEXT, " + KEY_CAND_TEMPAT_LAHIR
                + " TEXT, " + KEY_CAND_TGL_LAHIR + " TEXT, " + KEY_CAND_STATUS
                + " TEXT, " + KEY_CAND_PASANGAN + " TEXT, " + KEY_CAND_JML_ANAK
                + " TEXT, " + KEY_CAND_KELURAHAN + " TEXT, "
                + KEY_CAND_KECAMATAN + " TEXT, " + KEY_CAND_KABUPATEN
                + " TEXT, " + KEY_CAND_PROVINSI + " TEXT, " + KEY_CAND_BIOGRAFI
                + " TEXT " + ")";

        String create_table_pendidikan = "CREATE TABLE " + TABLE_PENDIDIKAN
                + "(" + KEY_PEK_ID_CAND + " TEXT, " + KEY_PEND_ID
                + " INTEGER, " + KEY_PEND_RINGKASAN + " TEXT, "
                + KEY_PEND_TGL_MULAI + " TEXT, " + KEY_PEND_TGL_SELESAI
                + " TEXT " + ")";

        String create_table_pekerjaan = "CREATE TABLE " + TABLE_PEKERJAAN + "("
                + KEY_PEK_ID_CAND + " TEXT, " + KEY_PEK_ID + " INTEGER, "
                + KEY_PEK_RINGKASAN + " TEXT, " + KEY_PEK_TGL_MULAI + " TEXT, "
                + KEY_PEK_TGL_SELESAI + " TEXT " + ")";

        String create_table_organisasi = "CREATE TABLE " + TABLE_ORGANISASI
                + "(" + KEY_ORG_ID_CAND + " TEXT, " + KEY_ORG_ID + " INTEGER, "
                + KEY_ORG_JABATAN + " TEXT, " + KEY_ORG_RINGKASAN + " TEXT, "
                + KEY_ORG_TGL_MULAI + " TEXT, " + KEY_ORG_TGL_SELESAI
                + " TEXT " + ")";

        String create_table_penghargaan = "CREATE TABLE " + TABLE_PENGHARGAAN
                + "(" + KEY_PENG_ID_CAND + " TEXT, " + KEY_PENG_ID
                + " INTEGER, " + KEY_PENG_INSTITUSI + " TEXT, "
                + KEY_PENG_RINGKASAN + " TEXT, " + KEY_PENG_TGL + " TEXT "
                + ")";

        String create_table_event = "CREATE TABLE " + TABLE_EVENT + "("
                + KEY_EVENT_ID + " INTEGER PRIMARY KEY, " + KEY_EVENT_DESKRIPSI
                + " TEXT, " + KEY_EVENT_JUDUL + " TEXT, " + KEY_EVENT_TANGGAL
                + " TEXT, " + KEY_EVENT_WAKTU + " TEXT " + ")";

        String create_table_janji = "CREATE TABLE " + TABLE_JANJI + "("
                + KEY_JANJI_ID_CAND + " TEXT, " + KEY_JANJI_CONTEXT + " TEXT, "
                + KEY_JANJI + " TEXT, " + KEY_JANJI_TGL + " TEXT, "
                + KEY_JANJI_SUMBER + " TEXT " + ")";

        String create_table_config = "CREATE TABLE " + TABLE_CONFIG + "("
                + KEY_CONF_FIRST + " TEXT " + ")";

        String create_table_vote = "CREATE TABLE " + TABLE_VOTE + "("
                + KEY_CONF_VOTE + " TEXT " + ")";

        db.execSQL(create_table_pendidikan);
        db.execSQL(create_table_organisasi);
        db.execSQL(create_table_pekerjaan);
        db.execSQL(create_table_penghargaan);
        db.execSQL(create_table_berita);
        db.execSQL(create_table_config);
        db.execSQL(create_table_vote);
        db.execSQL(create_table_candidate);
        db.execSQL(create_table_janji);
        db.execSQL(create_table_event);
        Log.i("DB", "DB has been created!");

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BERITA);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONFIG);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_VOTE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CANDIDATE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_JANJI);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORGANISASI);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PEKERJAAN);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PENDIDIKAN);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PENGHARGAAN);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_EVENT);
        onCreate(db);
    }

    public void addBerita(E_Berita berita) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_BERITA_ID, berita.getId());
        val.put(KEY_BERITA_TITLE, berita.getTitle());
        val.put(KEY_BERITA_CONTENT, berita.getContent());
        val.put(KEY_BERITA_EXCERPT, berita.getExcerpt());
        val.put(KEY_BERITA_DATE, berita.getDate());
        val.put(KEY_BERITA_AUTHOR, berita.getAuthor());
        val.put(KEY_BERITA_URL, berita.getUrl());
        db.insert(TABLE_BERITA, null, val);
        db.close();
        Log.i("DB", "BERITA has been inserted!");
    }

    public String getVote() {
        String vote = "";
        SQLiteDatabase db = this.getWritableDatabase();
        String select_vote = "SELECT " + KEY_CONF_VOTE + " FROM " + TABLE_VOTE;
        Cursor c = db.rawQuery(select_vote, null);
        if (c.moveToFirst()) {
            vote = c.getString(c.getColumnIndex(KEY_CONF_VOTE));
        }
        c.close();
        db.close();
        return vote;

    }

    public void addConfig() {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_CONF_FIRST, "1");
        db.insert(TABLE_CONFIG, null, val);
        db.close();
    }

    public void addVote(String Vote) {
        Utils.TRACE("DBClass", "addVote : ");
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_CONF_VOTE, Vote);
        db.insert(TABLE_VOTE, null, val);
        db.close();
    }

    public List<E_Berita> getBerita() {
        List<E_Berita> berita = new ArrayList<E_Berita>();
        String selectBerita = "SELECT " + KEY_BERITA_ID + ", "
                + KEY_BERITA_TITLE + ", " + KEY_BERITA_DATE + ", "
                + KEY_BERITA_URL + ", " + KEY_BERITA_EXCERPT + ", "
                + KEY_BERITA_CONTENT + ", " + KEY_BERITA_AUTHOR + " FROM "
                + TABLE_BERITA + " ORDER BY " + KEY_BERITA_ID + " DESC ";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(selectBerita, null);
        if (c.moveToFirst()) {
            do {
                E_Berita b = new E_Berita();
                b.setId(c.getInt(c.getColumnIndex(KEY_BERITA_ID)));
                b.setTitle(c.getString(c.getColumnIndex(KEY_BERITA_TITLE)));
                b.setDate(c.getString(c.getColumnIndex(KEY_BERITA_DATE)));
                b.setContent(c.getString(c.getColumnIndex(KEY_BERITA_CONTENT)));
                b.setExcerpt(c.getString(c.getColumnIndex(KEY_BERITA_EXCERPT)));
                b.setAuthor(c.getString(c.getColumnIndex(KEY_BERITA_AUTHOR)));
                b.setUrl(c.getString(c.getColumnIndex(KEY_BERITA_URL)));
                berita.add(b);
            } while (c.moveToNext());
        }

        c.close();
        db.close();
        return berita;

    }

    public boolean getIsPertama() {
        String cek = "SELECT " + KEY_CONF_FIRST + " FROM " + TABLE_CONFIG;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cur = db.rawQuery(cek, null);
        int i = 0;
        if (cur.moveToFirst()) {
            do {
                i++;
            } while (cur.moveToNext());
        }
        cur.close();
        db.close();
        if (i > 0)
            return false;
        else
            return true;
    }

    public boolean cekVoted() {
        String select_vote = "SELECT " + KEY_CONF_VOTE + " FROM " + TABLE_VOTE;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cur = db.rawQuery(select_vote, null);
        int i = 0;
        if (cur.moveToFirst()) {
            do {
                i++;
            } while (cur.moveToNext());
        }
        cur.close();
        db.close();
        if (i > 0)
            return true;
        else
            return false;
    }

    public void insertCandidate(E_Candidate cand) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_CAND_AGAMA, cand.getAgama());
        val.put(KEY_CAND_BIOGRAFI, cand.getBiografi());
        val.put(KEY_CAND_ID, cand.getId());
        val.put(KEY_CAND_JK, cand.getJenis_kelamin());
        val.put(KEY_CAND_JML_ANAK, cand.getJumlah_anak());
        val.put(KEY_CAND_KABUPATEN, cand.getKabupaten());
        val.put(KEY_CAND_KECAMATAN, cand.getKecamatan());
        val.put(KEY_CAND_KELURAHAN, cand.getKelurahan());
        val.put(KEY_CAND_NAMA, cand.getNama());
        val.put(KEY_CAND_PASANGAN, cand.getNama_pasangan());
        val.put(KEY_CAND_PROVINSI, cand.getProvinsi());
        val.put(KEY_CAND_ROLE, cand.getRole());
        val.put(KEY_CAND_STATUS, cand.getStatus_perkawinan());
        val.put(KEY_CAND_TEMPAT_LAHIR, cand.getTempat_lahir());
        val.put(KEY_CAND_TGL_LAHIR, cand.getTgl_lahir());
        db.insert(TABLE_CANDIDATE, null, val);
        db.close();
        Log.i("DB", "Candidate has been inserted!");
    }

    public void insertOrganisasi(E_Organisasi org) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_ORG_ID, org.getId());
        val.put(KEY_ORG_ID_CAND, org.getId_candidate());
        val.put(KEY_ORG_JABATAN, org.getJabatan());
        val.put(KEY_ORG_RINGKASAN, org.getRingkasan());
        val.put(KEY_ORG_TGL_MULAI, org.getTgl_mulai());
        val.put(KEY_ORG_TGL_SELESAI, org.getTgl_selesai());
        db.insert(TABLE_ORGANISASI, null, val);
        db.close();
        Log.i("DB", "Organisasi has been inserted!");
    }

    public void insertPekerjaan(E_Pekerjaan pek) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_PEK_ID, pek.getId());
        val.put(KEY_PEK_ID_CAND, pek.getId_candidate());
        val.put(KEY_PEK_RINGKASAN, pek.getRingkasan());
        val.put(KEY_PEK_TGL_MULAI, pek.getTgl_mulai());
        val.put(KEY_PEK_TGL_SELESAI, pek.getTgl_selsai());
        db.insert(TABLE_PEKERJAAN, null, val);
        db.close();
        Log.i("DB", "Pekerjaan has been inserted!");
    }

    public void insertPendidikan(E_Pendidikan pend) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_PEND_ID, pend.getId());
        val.put(KEY_PEK_ID_CAND, pend.getId_candidate());
        val.put(KEY_PEND_RINGKASAN, pend.getRingkasan());
        val.put(KEY_PEND_TGL_MULAI, pend.getTgl_mulai());
        val.put(KEY_PEND_TGL_SELESAI, pend.getTgl_selesai());
        db.insert(TABLE_PENDIDIKAN, null, val);
        db.close();
        Log.i("DB", "Pendidikan has been inserted!");
    }

    public void insertPenghargaan(E_Penghargaan peng) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_PENG_ID, peng.getId());
        val.put(KEY_PENG_ID_CAND, peng.getId_candidate());
        val.put(KEY_PENG_INSTITUSI, peng.getInstitusi());
        val.put(KEY_PENG_RINGKASAN, peng.getRingkasan());
        val.put(KEY_PENG_TGL, peng.getTgl());
        db.insert(TABLE_PENGHARGAAN, null, val);
        db.close();
        Log.i("DB", "Penghargaan has been inserted!");
    }

    public void insertEvent(E_Event event) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_EVENT_ID, event.getId());
        val.put(KEY_EVENT_DESKRIPSI, event.getDeskripsi());
        val.put(KEY_EVENT_JUDUL, event.getJudul());
        val.put(KEY_EVENT_TANGGAL, event.getTanggal_mulai());
        val.put(KEY_EVENT_WAKTU, event.getWaktu_mulai());
        db.insert(TABLE_EVENT, null, val);
        db.close();
        Log.i("DB", "Event has been inserted!");
    }

    public void insertJanji(E_Janji janji) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues val = new ContentValues();
        val.put(KEY_JANJI_ID_CAND, janji.getId_calon());
        val.put(KEY_JANJI_CONTEXT, janji.getContext_janji());
        val.put(KEY_JANJI, janji.getJanji());
        val.put(KEY_JANJI_TGL, janji.getTanggal());
        val.put(KEY_JANJI_SUMBER, janji.getJudul_sumber());
        db.insert(TABLE_JANJI, null, val);
        db.close();
        Log.i("DB", "Janji has been inserted!");
    }

    public List<E_Event> selectEvent() {
        List<E_Event> event = new ArrayList<E_Event>();
        String selectEvent = "SELECT " + KEY_EVENT_ID + ", "
                + KEY_EVENT_DESKRIPSI + ", " + KEY_EVENT_JUDUL + ", "
                + KEY_EVENT_TANGGAL + ", " + KEY_EVENT_WAKTU + " FROM "
                + TABLE_EVENT + " ORDER BY " + KEY_EVENT_TANGGAL + " DESC ";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(selectEvent, null);
        if (c.moveToFirst()) {
            do {
                E_Event e = new E_Event();
                e.setId(c.getInt(c.getColumnIndex(KEY_EVENT_ID)));
                e.setDeskripsi(c.getString(c
                        .getColumnIndex(KEY_EVENT_DESKRIPSI)));
                e.setJudul(c.getString(c.getColumnIndex(KEY_EVENT_JUDUL)));
                e.setTanggal_mulai(c.getString(c
                        .getColumnIndex(KEY_EVENT_TANGGAL)));
                e.setWaktu_mulai(c.getString(c.getColumnIndex(KEY_EVENT_WAKTU)));
                event.add(e);
            } while (c.moveToNext());
        }

        c.close();
        db.close();
        return event;

    }

    public List<E_Janji> selectJanji(String id_capres, String id_cawapres) {
        List<E_Janji> janji = new ArrayList<E_Janji>();
        String select = "SELECT " + KEY_JANJI_ID_CAND + ", "
                + KEY_JANJI_CONTEXT + ", " + KEY_JANJI + ", " + KEY_JANJI_TGL
                + ", " + KEY_JANJI_SUMBER + " FROM " + TABLE_JANJI + " WHERE "
                + KEY_JANJI_ID_CAND + " = '" + id_capres + "' OR '" + id_cawapres + "'";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select, null);
        Utils.TRACE("DBClass", "select query Janji length : " + c.getCount());
        if (c.moveToFirst()) {
            do {
                E_Janji j = new E_Janji();
                j.setId_calon(c.getString(c.getColumnIndex(KEY_JANJI_ID_CAND)));
                j.setContext_janji(c.getString(c
                        .getColumnIndex(KEY_JANJI_CONTEXT)));
                j.setJanji(c.getString(c.getColumnIndex(KEY_JANJI)));
                j.setJudul_sumber(c.getString(c
                        .getColumnIndex(KEY_JANJI_SUMBER)));
                j.setTanggal(c.getString(c.getColumnIndex(KEY_JANJI_TGL)));
                janji.add(j);
            } while (c.moveToNext());
        }

        c.close();
        db.close();
        return janji;

    }

    public List<E_Candidate> selectCandidate(String id) {
        List<E_Candidate> cand = new ArrayList<E_Candidate>();
        String select = "SELECT " + KEY_CAND_ID + ", " + KEY_CAND_AGAMA + ", "
                + KEY_CAND_BIOGRAFI + ", " + KEY_CAND_JK + ", "
                + KEY_CAND_JML_ANAK + ", " + KEY_CAND_KABUPATEN + ", "
                + KEY_CAND_KECAMATAN + ", " + KEY_CAND_KELURAHAN + ", "
                + KEY_CAND_NAMA + ", " + KEY_CAND_PASANGAN + ", "
                + KEY_CAND_PROVINSI + ", " + KEY_CAND_ROLE + ", "
                + KEY_CAND_STATUS + ", " + KEY_CAND_TEMPAT_LAHIR + ", "
                + KEY_CAND_TGL_LAHIR + " FROM " + TABLE_CANDIDATE + " WHERE "
                + KEY_CAND_ID + " = '" + id + "'";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select, null);

        if (c.moveToFirst()) {
            do {
                E_Candidate e = new E_Candidate();
                e.setAgama(c.getString(c.getColumnIndex(KEY_CAND_AGAMA)));
                e.setBiografi(c.getString(c.getColumnIndex(KEY_CAND_BIOGRAFI)));
                e.setId(c.getString(c.getColumnIndex(KEY_CAND_ID)));
                e.setJenis_kelamin(c.getString(c.getColumnIndex(KEY_CAND_JK)));
                e.setJumlah_anak(c.getString(c
                        .getColumnIndex(KEY_CAND_JML_ANAK)));
                e.setKabupaten(c.getString(c.getColumnIndex(KEY_CAND_KABUPATEN)));
                e.setKecamatan(c.getString(c.getColumnIndex(KEY_CAND_KECAMATAN)));
                e.setKelurahan(c.getString(c.getColumnIndex(KEY_CAND_KELURAHAN)));
                e.setNama(c.getString(c.getColumnIndex(KEY_CAND_NAMA)));
                e.setNama_pasangan(c.getString(c
                        .getColumnIndex(KEY_CAND_PASANGAN)));
                e.setProvinsi(c.getString(c.getColumnIndex(KEY_CAND_PROVINSI)));
                e.setRole(c.getString(c.getColumnIndex(KEY_CAND_ROLE)));
                e.setStatus_perkawinan(c.getString(c
                        .getColumnIndex(KEY_CAND_STATUS)));
                e.setTempat_lahir(c.getString(c
                        .getColumnIndex(KEY_CAND_TEMPAT_LAHIR)));
                e.setTgl_lahir(c.getString(c.getColumnIndex(KEY_CAND_TGL_LAHIR)));
                cand.add(e);
            } while (c.moveToNext());
        }
        c.close();
        db.close();
        return cand;
    }

    public List<E_Pendidikan> selectPendidikan(String id) {
        List<E_Pendidikan> pend = new ArrayList<E_Pendidikan>();
        String select = "SELECT " + KEY_PEND_ID + ", " + KEY_PEND_ID_CAND
                + ", " + KEY_PEND_RINGKASAN + ", " + KEY_PEND_TGL_MULAI + ", "
                + KEY_PEND_TGL_SELESAI + " FROM " + TABLE_PENDIDIKAN
                + " WHERE " + KEY_PEND_ID_CAND + " = '" + id + "'";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select, null);
        if (c.moveToFirst()) {
            do {
                E_Pendidikan p = new E_Pendidikan();
                p.setId(c.getInt(c.getColumnIndex(KEY_PEND_ID)));
                p.setId_candidate(c.getString(c
                        .getColumnIndex(KEY_PEND_ID_CAND)));
                p.setRingkasan(c.getString(c.getColumnIndex(KEY_PEND_RINGKASAN)));
                p.setTgl_mulai(c.getString(c.getColumnIndex(KEY_PEND_TGL_MULAI)));
                p.setTgl_selesai(c.getString(c
                        .getColumnIndex(KEY_PEND_TGL_SELESAI)));
                pend.add(p);
            } while (c.moveToNext());
        }

        c.close();
        db.close();

        return pend;
    }

    public List<E_Pekerjaan> selectPekerjaan(String id) {
        List<E_Pekerjaan> pek = new ArrayList<E_Pekerjaan>();
        String select = "SELECT " + KEY_PEK_ID + ", " + KEY_PEK_ID_CAND + ", "
                + KEY_PEK_RINGKASAN + ", " + KEY_PEK_TGL_MULAI + ", "
                + KEY_PEK_TGL_SELESAI + " FROM " + TABLE_PEKERJAAN + " WHERE "
                + KEY_PEK_ID_CAND + " = '" + id + "'";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select, null);
        if (c.moveToFirst()) {
            do {
                E_Pekerjaan e = new E_Pekerjaan();
                e.setId(c.getInt(c.getColumnIndex(KEY_PEK_ID)));
                e.setId_candidate(c.getString(c.getColumnIndex(KEY_PEK_ID_CAND)));
                e.setRingkasan(c.getString(c.getColumnIndex(KEY_PEK_RINGKASAN)));
                e.setTgl_mulai(c.getString(c.getColumnIndex(KEY_PEK_TGL_MULAI)));
                e.setTgl_selsai(c.getString(c
                        .getColumnIndex(KEY_PEK_TGL_SELESAI)));
                pek.add(e);
            } while (c.moveToNext());
        }

        c.close();
        db.close();

        return pek;
    }

    public List<E_Organisasi> selectOrganisasi(String id) {
        List<E_Organisasi> org = new ArrayList<E_Organisasi>();
        String select = "SELECT " + KEY_ORG_ID + ", " + KEY_ORG_ID_CAND + ", "
                + KEY_ORG_JABATAN + ", " + KEY_ORG_RINGKASAN + ", "
                + KEY_ORG_TGL_MULAI + ", " + KEY_ORG_TGL_SELESAI + " FROM "
                + TABLE_ORGANISASI + " WHERE " + KEY_ORG_ID_CAND + " = '" + id + "'";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select, null);
        if (c.moveToFirst()) {
            do {
                E_Organisasi o = new E_Organisasi();
                o.setId(c.getInt(c.getColumnIndex(KEY_ORG_ID)));
                o.setId_candidate(c.getString(c.getColumnIndex(KEY_ORG_ID_CAND)));
                o.setJabatan(c.getString(c.getColumnIndex(KEY_ORG_JABATAN)));
                o.setRingkasan(c.getString(c.getColumnIndex(KEY_ORG_RINGKASAN)));
                o.setTgl_mulai(c.getString(c.getColumnIndex(KEY_ORG_TGL_MULAI)));
                o.setTgl_selesai(c.getString(c
                        .getColumnIndex(KEY_ORG_TGL_SELESAI)));
                org.add(o);
            } while (c.moveToNext());
        }

        c.close();
        db.close();

        return org;
    }

    public List<E_Penghargaan> selectPenghargaan(String id) {
        List<E_Penghargaan> peng = new ArrayList<E_Penghargaan>();
        String select = "SELECT " + KEY_PENG_ID + ", " + KEY_PENG_ID_CAND
                + ", " + KEY_PENG_INSTITUSI + ", " + KEY_PENG_RINGKASAN + ", "
                + KEY_PENG_TGL + " FROM " + TABLE_PENGHARGAAN + " WHERE "
                + KEY_PENG_ID_CAND + " = '" + id + "'";

        SQLiteDatabase db = this.getWritableDatabase();
        Cursor c = db.rawQuery(select, null);
        if (c.moveToFirst()) {
            do {
                E_Penghargaan p = new E_Penghargaan();
                p.setId(c.getInt(c.getColumnIndex(KEY_PENG_ID)));
                p.setId_candidate(c.getString(c
                        .getColumnIndex(KEY_PENG_ID_CAND)));
                p.setInstitusi(c.getString(c.getColumnIndex(KEY_PENG_INSTITUSI)));
                p.setRingkasan(c.getString(c.getColumnIndex(KEY_PENG_RINGKASAN)));
                p.setTgl(c.getString(c.getColumnIndex(KEY_PENG_TGL)));
                peng.add(p);
            } while (c.moveToNext());
        }
        c.close();
        db.close();

        return peng;
    }
}
